﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace F_SER.DAL
{
    public class SqlHelper
    {
        #region 通用方法
        // 数据连接池
        private SqlConnection con;
        /// <summary>
        /// 返回数据库连接字符串
        /// </summary>
        /// <returns></returns>
        public static String GetSqlConnection()
        {
            String conn = ConfigurationManager.ConnectionStrings["f_test"].ToString();//调用Web.config 连接字符串
            // String conn = "server=. ;uid=sa; pwd=123;database=421124_2011";
            return conn;
        }
        #endregion
      

        #region 保存方法(已检查非法字符)
        /// <summary>
        /// dataGridView的增删改后的保存方法 
        /// </summary>
        /// <param name="ap_set">dataGridView绑定的数据源</param>
        /// <param name="ProName">存储过程名字</param>
        /// <returns></returns>
        public string f_save(DataSet ap_set, string ProName, SqlParameter[] prams)
        {
            DataSet ld_set = new DataSet();
            String ConnStr = GetSqlConnection();//获取连接字符串
            SqlCommand cmd = CreateCommand(ProName, prams);
            System.Data.SqlClient.SqlDataAdapter p_ada = new System.Data.SqlClient.SqlDataAdapter(cmd);
            try
            {

                p_ada.Fill(ld_set);
                ld_set.Tables[0].PrimaryKey = new DataColumn[] { ld_set.Tables[0].Columns[0] };//设置主键
                SqlCommandBuilder cmdb = new SqlCommandBuilder(p_ada);

                if (ap_set.Tables[0].GetChanges() != null)
                {
                    p_ada.Update(ap_set);
                    ap_set.AcceptChanges();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return "ZZ00ZZ";
        }

        public string f_saveTable(DataTable ap_set, string ProName, SqlParameter[] prams)
        {
            DataSet ld_set = new DataSet();
            String ConnStr = GetSqlConnection();//获取连接字符串
            SqlCommand cmd = CreateCommand(ProName, prams);
            System.Data.SqlClient.SqlDataAdapter p_ada = new System.Data.SqlClient.SqlDataAdapter(cmd);
            try
            {
                p_ada.Fill(ld_set);
                ld_set.Tables[0].PrimaryKey = new DataColumn[] { ld_set.Tables[0].Columns[0] };//设置主键
                SqlCommandBuilder cmdb = new SqlCommandBuilder(p_ada);

                if (ap_set.GetChanges() != null)
                {
                    p_ada.Update(ap_set);
                    ap_set.AcceptChanges();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return "ZZ00ZZ";
        }
        #endregion

        #region 检查非法字符
        /// <summary>
        /// 检查参数是否包含敏感字符
        /// </summary>
        /// <param name="prams"></param>
        /// <returns>true存在敏感字符</returns>
        public bool checkPrams(SqlParameter[] prams)
        {
            bool resule = false;
            foreach (var item in prams)
            {

                if (item .Value!=null&& (item.Value.ToString().ToLower().IndexOf("select") != -1 ||
                    item.Value.ToString().ToLower().IndexOf("update") != -1 ||
                    item.Value.ToString().ToLower().IndexOf("insert") != -1 ||
                    item.Value.ToString().ToLower().IndexOf("delete") != -1)
                    )
                {
                    resule = true;
                }

            }
            return resule;
        }
        public static bool checkPramStat(SqlParameter[] prams)
        {
            bool resule = false;
            foreach (var item in prams)
            {
                if (item.Value.ToString().ToLower().IndexOf("select") != -1 ||
                    item.Value.ToString().ToLower().IndexOf("update") != -1 ||
                    item.Value.ToString().ToLower().IndexOf("insert") != -1 ||
                    item.Value.ToString().ToLower().IndexOf("delete") != -1
                    )
                {
                    resule = true;
                }

            }
            return resule;
        }
        public static bool checkPramStat(SqlParameter prams)
        {
            bool resule = false;

            if (prams.Value.ToString().ToLower().IndexOf("select") != -1 ||
                    prams.Value.ToString().ToLower().IndexOf("update") != -1 ||
                    prams.Value.ToString().ToLower().IndexOf("insert") != -1 ||
                    prams.Value.ToString().ToLower().IndexOf("delete") != -1
                    )
                {
                    resule = true;
                }

            
            return resule;
        }
        #endregion

        #region 执行sql字符串
        /// <summary>
        /// 执行不带参数的SQL语句
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public static int ExecuteSql(String Sqlstr)
        {
            String ConnStr = GetSqlConnection();//获取连接字符串
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;//连接
                cmd.CommandText = Sqlstr;//准备做什么
                conn.Open();
                int i = cmd.ExecuteNonQuery();//对象是对insert,update,delete语句返回受影响的行数
                conn.Close();
                return i;
            }
        }

        /// <summary>  
        /// 执行带参数的SQL语句  
        /// </summary>  
        /// <param name="Sqlstr">SQL语句</param>  
        /// <param name="param">参数对象数组</param>  
        /// <returns></returns>  
        public static int ExecuteSql(String Sqlstr, SqlParameter[] param)
        {
            if (checkPramStat(param))
            {
                throw new Exception("参数存在不合法字符!");
            }
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                cmd.Parameters.AddRange(param);
                conn.Open();
                int i = cmd.ExecuteNonQuery();
                conn.Close();
                return i;
            }
        }
        /// <summary>
        /// 返回结果集第一行第一列
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string cmdText)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = cmdText;
                    return cmd.ExecuteScalar();
                }
            }
        }
        /// <summary>
        /// 返回结果集第一行第一列
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string cmdText, params SqlParameter[] param)
        {
            if (checkPramStat(param))
            {
                throw new Exception("参数存在不合法字符!");
            }
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = cmdText;
                    cmd.Parameters.AddRange(param);
                    return cmd.ExecuteScalar();
                }
            }
        }
        /// <summary>  
        /// 执行SQL语句返回DataReader  
        /// </summary>  
        /// <param name="Sqlstr"></param>  
        /// <returns></returns>  
        public static SqlDataReader ExecuteReader(String Sqlstr)
        {
            String ConnStr = GetSqlConnection();
            SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的  
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection  
            }
            catch //(Exception ex)  
            {
                return null;
            }
        }
        /// <summary>
        /// 执行SQL语句返回DataReader  
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(String Sqlstr, SqlParameter param)
        {
            if (checkPramStat(param))
            {
                throw new Exception("参数存在不合法字符!");
            }
            String ConnStr = GetSqlConnection();
            SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的  
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                cmd.Parameters.Add(param);
                conn.Open();
                return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection  
            }
            catch //(Exception ex)  
            {
                return null;
            }
        }
        /// <summary>  
        /// 执行带参数 SQL语句返回DataReader  
        /// </summary>  
        /// <param name="Sqlstr"></param>  
        /// <returns></returns>  
        public static SqlDataReader ExecuteReader(String Sqlstr, SqlParameter[] param)
        {
            if (checkPramStat(param))
            {
                throw new Exception("参数存在不合法字符!");
            }
            String ConnStr = GetSqlConnection();
            SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的  
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                cmd.Parameters.AddRange(param);
                conn.Open();
                return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection  
            }
            catch //(Exception ex)  
            {
                return null;
            }
        }

        /// <summary>  
        /// 执行SQL语句并返回数据表  
        /// </summary>  
        /// <param name="Sqlstr">SQL语句</param>  
        /// <returns>DataTable</returns>  
        public static DataTable ExecuteDt(String Sqlstr)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                DataTable dt = new DataTable();
                conn.Open();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
        }
        /// <summary>
        /// 执行带参数SQL语句并返回数据表
        /// </summary>
        /// <param name="Sqlstr">sql语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public static DataTable ExecuteDt(String Sqlstr, SqlParameter[] param)
        {
            if (checkPramStat(param))
            {
                throw new Exception("参数存在不合法字符!");
            }
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))//连接字符串 用完会释放内存
            {
                SqlCommand cmd = new SqlCommand(Sqlstr, conn); //建立cmd命令
                cmd.Parameters.AddRange(param);            //将参数添加到Sqlstr语句里面

                //用于填充 DataSet 和更新 SQL Server 数据库的一组数据命令和一个数据库连接
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                conn.Open();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
        }
        /// <summary>  
        /// 执行SQL语句并返回DataSet  
        /// </summary>  
        /// <param name="Sqlstr">SQL语句</param>  
        /// <returns></returns>  
        public static DataSet ExecuteDs(String Sqlstr)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                DataSet ds = new DataSet();
                conn.Open();
                da.Fill(ds);
                conn.Close();
                return ds;
            }
        }



        #endregion

        #region 操作存储过程
        /// <summary>
        /// 运行存储过程(已重载)
        /// </summary>
        /// <param name="procName">存储过程的名字</param>
        /// <returns>存储过程的返回值</returns>
        public int RunProc(string procName)
        {
            SqlCommand cmd = CreateCommand(procName, null);
            cmd.ExecuteNonQuery();
            this.Close();
            return (int)cmd.Parameters["ReturnValue"].Value;
        }
        /// <summary>
        /// 运行存储过程(已重载)
        /// </summary>
        /// <param name="procName">存储过程的名字</param>
        /// <param name="prams">存储过程的输入参数列表</param>
        /// <returns>存储过程的返回值</returns>
        public void RunProc(string procName, SqlParameter[] prams)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            cmd.ExecuteNonQuery();
            this.Close();
            //return (int)cmd.Parameters[1].Value;
        }
        /// <summary>
        /// 运行存储过程(已重载)
        /// </summary>
        /// <param name="procName">存储过程的名字</param>
        /// <param name="dataReader">结果集</param>
        public void RunProc(string procName, out SqlDataReader dataReader)
        {
            SqlCommand cmd = CreateCommand(procName, null);
            dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }
        /// <summary>
        /// 运行存储过程(已重载)
        /// </summary>
        /// <param name="procName">存储过程的名字</param>
        /// <param name="prams">存储过程的输入参数列表</param>
        /// <param name="dataReader">结果集</param>
        public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

        }
        //*****************************
        /// <summary>
        /// 运行存储过程(已重载)
        /// </summary>
        /// <param name="procName">存储过程的名字</param>
        /// <param name="prams">存储过程的输入参数列表</param>
        /// <returns>结果集DataTable</returns>
        public DataTable RunProcTable(string procName, SqlParameter[] prams)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            Close();
            return dt;
        }
        /// <summary>
        ///  运行存储过程
        /// </summary>
        /// <param name="procName">存储过程的名字</param>
        /// <returns>DataSet</returns>
        public DataSet RunProcDataSet(string procName)
        {
            SqlCommand cmd = CreateCommand(procName);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet dt = new DataSet();
            da.Fill(dt);
            Close();
            return dt;
        }
        /// <summary>
        ///  运行存储过程
        /// </summary>
        /// <param name="procName">存储过程的名字</param>
        /// <param name="prams">参数</param>
        /// <returns>DataSet</returns>
        public DataSet RunProcDataSet(string procName, SqlParameter[] prams)
        {

            SqlCommand cmd = CreateCommand(procName, prams);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet dt = new DataSet();
            da.Fill(dt);
            Close();
            return dt;
        }
        //****************************
        /// <summary>
        /// 创建Command对象用于访问存储过程
        /// </summary>
        /// <param name="procName">存储过程的名字</param>
        /// <param name="prams">存储过程的输入参数列表</param>
        /// <returns>Command对象</returns>
        private SqlCommand CreateCommand(string procName)
        {
            // 确定连接是打开的
            Open();

            SqlCommand cmd = new SqlCommand(procName, con);
            cmd.CommandType = CommandType.StoredProcedure;

            // 返回Command对象
            return cmd;
        }

        /// <summary>
        /// 创建Command对象用于访问存储过程
        /// </summary>
        /// <param name="procName">存储过程的名字</param>
        /// <param name="prams">存储过程的输入参数列表</param>
        /// <returns>Command对象</returns>
        private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
        {
            // 确定连接是打开的
            Open();
            //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );
            SqlCommand cmd = new SqlCommand(procName, con);
            cmd.CommandType = CommandType.StoredProcedure;
            if (checkPrams(prams))
            {
                throw new Exception("参数存在不合法字符!");
            }
            // 添加存储过程的输入参数列表
            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                    cmd.Parameters.Add(parameter);
            }
            // 返回Command对象
            return cmd;
        }
        /// <summary>
        /// 创建输入参数
        /// </summary>
        /// <param name="ParamName">参数名</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <param name="Value">参数值</param>
        /// <returns>新参数对象</returns>
        public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }
        /// <summary>
        /// 创建输出参数
        /// </summary>
        /// <param name="ParamName">参数名</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <returns>新参数对象</returns>
        public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
        }
        /// <summary>
        /// 创建存储过程参数
        /// </summary>
        /// <param name="ParamName">参数名</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <param name="Direction">参数的方向(输入/输出)</param>
        /// <param name="Value">参数值</param>
        /// <returns>新参数对象</returns>
        public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            SqlParameter param;
            if (Size > 0)
            {
                param = new SqlParameter(ParamName, DbType, Size);
            }
            else
            {
                param = new SqlParameter(ParamName, DbType);
            }
            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
            {
                param.Value = Value;
            }
            return param;
        }
        #endregion

        #region 数据库连接和关闭
        /// <summary>
        /// 打开连接池
        /// </summary>
        private void Open()
        {
            // 打开连接池
            if (con == null)
            {
                //这里不仅需要using System.Configuration;还要在引用目录里添加
                con = new SqlConnection(GetSqlConnection());
                con.Open();
            }
        }
        /// <summary>
        /// 关闭连接池
        /// </summary>
        public void Close()
        {
            if (con != null)
                con.Close();
        }
        /// <summary>
        /// 释放连接池
        /// </summary>
        public void Dispose()
        {
            // 确定连接已关闭
            if (con != null)
            {
                con.Dispose();
                con = null;
            }
        }
        #endregion


    }
}